import json

import openpyxl
from django.test import TestCase

# Create your tests here.
# import re
#
# def validate_dict(dictionary):
#     expected_values = {
#         'x': lambda value: value.isdigit() or value == '',
#         'v': lambda value: value == 'news',
#         'o': lambda value: re.match(r'^\d+(\.\d+){2}$', value) is not None
#     }
#
#     validation_result = {}
#
#     for key, value in dictionary.items():
#         if key in expected_values:
#             if expected_values[key](value):
#                 validation_result[key] = value
#             else:
#                 validation_result[key] = f'{value}[校验不通过，预期值为]'
#         else:
#             validation_result[key] = f'无效的键：{key}'
#
#     if len(validation_result) == 0:
#         return 1
#     else:
#         return validation_result

import re

#
# def validate_dict(dictionary):
#     expected_values = {
#         'x': lambda value: value.isdigit() or value == '',
#         'v': lambda value: value == 'news',
#         'o': lambda value: re.match(r'^\d+(\.\d+){2}$', value) is not None
#     }
#
#     validation_result = {}
#
#     for key, value in dictionary.items():
#         if key in expected_values:
#             if expected_values[key](value):
#                 validation_result[key] = f'{value}[校验通过]'
#             else:
#                 validation_result[key] = f'{value}[不符合预期，预期值是：{expected_values[key]}]'
#         else:
#             validation_result[key] = f'无效的键：{key}'
#
#     return validation_result
# def validate_dict(dictionary):
#     expected_values = {
#         'x': {'expected': '数字或为空', 'validator': lambda value: value.isdigit() or value == ''},
#         'v': {'expected': 'news', 'validator': lambda value: value == 'news'},
#         'o': {'expected': 'n.n.n格式', 'validator': lambda value: re.match(r'^\d+(\.\d+){2}$', value) is not None}
#     }
#
#     validation_result = {}
#
#     for key, value in dictionary.items():
#         if key in expected_values:
#             validator = expected_values[key]['validator']
#             if validator(value):
#                 validation_result[key] = f'{value}[校验通过]'
#             else:
#                 expected = expected_values[key]['expected']
#                 validation_result[key] = f'{value}[不符合预期，预期值是：{expected}]'
#         else:
#             validation_result[key] = f'无效的键：{key}'
#
#     return validation_result
# import re
#
#
# def validate_dict(dictionary):
#     expected_values = {
#         'x': lambda value: value.isdigit() or value == '',
#         'v': lambda value: value == 'news',
#         'o': lambda value: re.match(r'^\d+(\.\d+){2}$', value) is not None
#     }
#
#     validation_result = {}
#
#     for key, value in dictionary.items():
#         if key in expected_values:
#             if expected_values[key](value):
#                 validation_result[key] = f'{value}[校验通过]'
#             else:
#                 validation_result[key] = f'{value}[不符合预期，预期值是：{expected_values[key].__name__[2:]}]'
#         else:
#             validation_result[key] = value
#
#     return validation_result
#
#
# a = {'x': '12', 'v': 'news', 'o': '6.7.1'}
# result = validate_dict(a)
import re

# def validate_dict(dictionary):
#     expected_values = {
#         'x': lambda value: value.isdigit() or value == '',
#         'v': lambda value: value == 'news',
#         'o': lambda value: re.match(r'^\d+(\.\d+){2}$', value) is not None
#     }
#
#     valid_values = {}
#     invalid_values = {}
#
#     for key, value in dictionary.items():
#         if key in expected_values:
#             if expected_values[key](value):
#                 valid_values[key] = f'{value}[校验通过]'
#             else:
#                 invalid_values[key] = f'{value}[不符合预期，预期值是：{expected_values[key].__name__[2:]}]'
#         else:
#             invalid_values[key] = f'无效的键：{key}'
#
#     return valid_values, invalid_values

import re

# def validate_dict(dictionary):
#     expected_values = {
#         'x': [re.compile(r'^\d+$'), '数字'],
#         'v': ['news', 'news'],
#         'o': [re.compile(r'^\d+(\.\d+){2}$'), '形如 x.y.z 的版本号']
#     }
#
#     valid_values = {}
#     invalid_values = {}
#
#     for key, value in dictionary.items():
#         if key in expected_values:
#             expected_value, expected_info = expected_values[key]
#             if isinstance(expected_value, list) and isinstance(value, (int, float)):
#                 valid_values[key] = f'{value}[校验通过]'
#             elif isinstance(expected_value, str) and value == expected_value:
#                 valid_values[key] = f'{value}[校验通过]'
#             elif isinstance(expected_value, re.Pattern) and expected_value.match(value):
#                 valid_values[key] = f'{value}[校验通过]'
#             else:
#                 invalid_values[key] = f'{value}[不符合预期，预期值是：{expected_info}]'
#         else:
#             valid_values[key] = f'{value}[校验通过,无预期值]'
#
#     return valid_values, invalid_values
# def validate_dict(dictionary):
#     # 定义预期值字典，使用字典的值存储预期值和预期信息
#     expected_values = {
#         'x': ([r'^\d+$'], '数字'),  # 使用列表存储正则表达式和预期信息
#         'v': (['news', '123'], 'news'),  # 使用列表存储字符串和预期信息
#         'o': ([r'^\d+(\.\d+){2}$'], '形如 x.y.z 的版本号'),  # 使用列表存储正则表达式和预期信息
#         'y': ([r'^.+'], '非空字符串')
#     }
#
#     valid_values = {}
#     invalid_values = {}
#
#     for key, value in dictionary.items():
#         if key in expected_values:
#             expected_value, expected_info = expected_values[key]
#             # # 如果值与预期值相等且类型为 str、int 或 float，则校验通过
#             if any(isinstance(expected_value, t) and value == expected_value for t in [str, int, float]):
#                 valid_values[key] = f'{value}[校验通过a]'
#             # 如果预期值是列表且任何一个正则表达式模式与值匹配，则校验通过
#             elif isinstance(expected_value, list) and any(re.match(pattern, value) for pattern in expected_value):
#                 valid_values[key] = f'{value}[校验通过b]'
#             # 如果预期值是正则表达式模式且与值完全匹配，则校验通过
#             elif isinstance(expected_value, re.Pattern) and expected_value.fullmatch(value):
#                 valid_values[key] = f'{value}[校验通过c]'
#             else:
#                 invalid_values[key] = f'{value}[不符合预期，预期值是：{expected_info}]'
#         else:
#             valid_values[key] = f'{value}[校验通过,无预期值]'
#
#     return valid_values, invalid_values
#
#
# a = {'x': '1', 'v': 'news', 'o': '6.7.1', 'y': '1'}
# valid_values, invalid_values = validate_dict(a)
# # test = dict(validate_dict(a))
# print(validate_dict(a))

# print('校验通过的值：')
# for key, value in valid_values.items():
#     print(f'{key}: {value}')
#
# print('\n校验不通过的值：')
# for key, value in invalid_values.items():
#     print(f'{key}: {value}')


# def compare_params(all_params, important_params, actual_params):
#     """
#
#     :param all_params:全部上报参数
#     :param important_params:重要上报参数
#     :param actual_params:真实上报参数
#     :return:
#     required_params_missing：缺失的参数
#     extra_params: 多余的参数
#     passed_params:校验通过参数
#     import_passed_params：通过校验正要参数
#     right_values：正确取值参数
#     wrong_values 错误取值参数
#     """
#     required_params_missing = list(set(all_params) - set(actual_params.keys()))
#     extra_params_key = list(set(actual_params.keys()) - set(all_params))
#     extra_params = {k: actual_params[k] for k in set(extra_params_key) & set(actual_params.keys())}
#     passed_params = {k: actual_params[k] for k in set(all_params) & set(actual_params.keys())}
#     passed_params_sorted = dict(sorted(passed_params.items(), key=lambda x: x[0]))
#     import_passed_params = {k: actual_params[k] for k in important_params if k in actual_params}
#     right_values, wrong_values = check_report_params_value(passed_params_sorted)
#
#     return required_params_missing, extra_params, passed_params_sorted, import_passed_params, right_values, wrong_values
#
#
# def output_result(required_params_missing, extra_params, passed_params_sorted, import_passed_params, right_values,
#                   wrong_values):
#     """
#
#     required_params_missing：缺失的参数
#     extra_params: 多余的参数
#     passed_params:校验通过参数
#     import_passed_params：通过校验正要参数
#     right_values：正确取值参数
#     wrong_values 错误取值参数
#     :return:
#     """
#     result = {}
#     itemspaceid = passed_params_sorted.get('itemspaceid')
#     if itemspaceid is None:
#         result['无法校验'] = '获取不到itemspaceid的值'
#     elif 'itemspaceid' not in passed_params_sorted:
#         result['无法校验'] = '不存在itemspaceid'
#     else:
#         if len(required_params_missing) == 0 and len(extra_params) == 0:
#             result['校验结果'] = '成功'
#             result['校验通过的参数'] = passed_params_sorted
#             result['重要参数'] = import_passed_params
#             result['【√校验通过】---参数存在且值正确'] = right_values
#             result['【×校验不通过】---参数存在，但值不符合预期，需要检查'] = wrong_values
#         else:
#             result['校验结果'] = '失败'
#             result['缺失参数'] = ','.join(required_params_missing)
#             result['新增参数'] = extra_params
#             # 校验通过的参数按照首字母进行排序
#             result['【√校验通过】---参数存在且值正确'] = right_values
#             result['【×校验不通过】---参数存在，但值不符合预期，需要检查'] = wrong_values
#
#     return result
#
#
# def check_report_params_value(passed_params):
#     """
#
#     :param passed_params: 校验通过的参数，继续校验取值
#     :return:
#     """
#     # 定义预期值字典，使用字典的值存储预期值和预期信息
#     expected_values = {
#         'appchn': ([r'^\d{4}$'], '渠道号为4位数字'),  # 使用列表存储正则表达式和预期信息
#         # 'appchn': ([r'^\d+$'], '数字'),  # 使用列表存储正则表达式和预期信息
#         'appid': (['news'], '新闻固定取值news'),  # 使用列表存储字符串和预期信息
#         'appv': ([r'^\d+(\.\d+){2}$'], '形如 x.y.z 的版本号'),  # 使用列表存储正则表达式和预期信息
#         'asid': ([r'^[A-F-9]{8}-[A-F-9]{4}-[A-F-9]{4}-[A-F-9]{4}-[A-F-9]{12}$'],
#                  '形如 C5872C34-6F15-4ECD-AABA-C677884C374E 的启动id'),
#         'cid': ([r'^\d{19}$'], 'cid为19位数字'),
#         'delaytrack': (['0', '1'], 'delaytrack取值0或1，=0首次上报，=1重试上报'),
#         'errorcode': (['0', '1'], 'errorcode取值0或1，=0首次上报，=1重试上报'),
#         'nwt': (['1000', '1001', '1002', '1004', '1004', '1005'], '移动网络'),
#         'os': (['iOS', 'Android'], '操作系统iOS或者Android'),
#         'path': (['0', '1'], '0或者1')
#
#     }
#
#     right_values = {}
#     wrong_values = {}
#
#     for key, value in passed_params.items():
#         if key in expected_values:
#             expected_value, expected_info = expected_values[key]
#             # 根据预期值的类型进行校验
#             if isinstance(expected_value, list) and any(re.match(pattern, value) for pattern in expected_value):
#                 right_values[key] = f'{value} [校验通过，预期值是：{expected_info}]'
#             else:
#                 wrong_values[key] = f'{value} [不符合预期，预期值是：{expected_info}]'
#         else:
#             right_values[key] = f'{value} [校验通过,无预期值]'
#
#     return right_values, wrong_values
#
#
# all_params = ['itemspaceid', 'X', 'B', 'W']  # 全部参数列表
# import_params = ['X']  # 重要参数列表
# real_params = {'itemspaceid': '1', 'X': '1', 'B': '2', 'ww': '44'}  # 真实上报参数字典
#
# missing_params, redundant_params, pass_params_sorted, valid_values, invalid_values, import_pass_params = compare_params(
#     all_params, import_params, real_params)
# output = output_result(missing_params, redundant_params, pass_params_sorted, valid_values, invalid_values,
#                        import_pass_params)
# print(output)
# def get_data_from_excel(all_params_filepath, itemspaceid, reporttype):
#     """
#     从Excel文件中获取指定广告位和上报类型对应的全部参数列表
#
#     :param all_params_filepath: Excel文件路径
#     :param itemspaceid: 广告位ID，用于获取对应的工作表
#     :param reporttype: 上报类型，例如 "v" 或 "av"
#     :return: 全部参数列表
#     """
#     # 打开储存双端广告位+上报类型对应的全部参数的Excel文件
#     workbook = openpyxl.load_workbook(all_params_filepath)
#     # 获取指定的工作表
#     try:
#         # 表里将不同的广告位分分成不同的sheet，根据传入的itemspaceid获取对应sheet表
#         worksheet = workbook[itemspaceid]
#         # 获取工作表的最大列数和行数
#         max_column = worksheet.max_column
#         max_row = worksheet.max_row
#         # 初始化数组，用于存储全部参数
#         all_params = []
#         # 遍历第一行的每个单元格
#         for column in range(1, max_column + 1):
#             # 获取第一行的每个单元格的值
#             cell_value = worksheet.cell(row=1, column=column).value
#             # 如果单元格的值等于上报类型
#             if cell_value == reporttype:
#                 report_type_column = column  # 获取上报类型所在列的索引
#                 break
#         # 遍历从第二行开始的每一行
#         for row in range(2, max_row + 1):
#             # 获取当前上报类型所在列的全部值
#             cell_value = worksheet.cell(row=row, column=report_type_column).value
#             # 将单元格里不为空的值都存在all_params数组内
#             if cell_value is not None:
#                 all_params.append(cell_value)
#         # 关闭Excel文件
#         workbook.close()
#         return all_params
#     except KeyError:
#         return []


# filepath = r'C:\Users\wenmanwei\PycharmProjects\AutoCheckParams\CheckParams\ReportDataExcel\iOS_Report__Params.xlsx'
# sheetname = '12224'
# reporttype = 'v'
# print(get_data_from_excel(filepath, sheetname, reporttype))


# def get_ad_chinese_template(ad_template, itemspaceid):
#     """
#
#     :param ad_template: 广告模板英文名字
#     :param itemspaceid: 广告位id
#     :return: 广告模板中文名
#     """
#     template_12224 = {
#         'apploading': '静图模板',
#         'h5_apploading': 'H5模板',
#         'h5video_apploading': '视频模板',
#         'apploading_fullscreen':'全屏静图模板',
#         'h5_apploading_fullscreen': '全屏H5模板',
#         'h5video_apploading_fullscreen': '全屏视频模板',
#         'lottie_apploading_fullscreen': '全屏lottile模板',
#         'combined_video_fullscreen': '双幕视频联动模板',
#         'h5_apploading_fullscreen_float': 'H5浮层模板',
#         'h5video_apploading_fullscreen_float': '视频浮层模板',
#         'multilevel_apploading_fullscreen': '多层卡片互动模板',
#         'multiple_apploading_fullscreen': '多卡切换模板',
#     }
#     template_12451 = {
#         'apploading': '静图模板',
#         'h5_apploading': 'H5模板',
#         'h5video_apploading': '视频模板',
#         'apploading_fullscreen':'全屏静图模板',
#         'h5_apploading_fullscreen': '全屏H5模板',
#         'h5video_apploading_fullscreen': '全屏视频模板',
#         'lottie_apploading_fullscreen': '全屏lottile模板',
#         'combined_video_fullscreen': '双幕视频联动模板',
#         'h5_apploading_fullscreen_float': 'H5浮层模板',
#         'h5video_apploading_fullscreen_float': '视频浮层模板',
#         'multilevel_apploading_fullscreen': '多层卡片互动模板',
#         'multiple_apploading_fullscreen': '多卡切换模板',
#     }
#     if itemspaceid == '12224':
#         if ad_template in template_12224:
#             return template_12224[ad_template]
#         else:
#             return "未知模板"
#     if itemspaceid == '12451':
#         if ad_template in template_12451:
#             return template_12451[ad_template]
#         else:
#             return "未知模板"


# def get_ad_template_name(ad_template, itemspaceid):
#     """
#
#     :param ad_template: 广告模板英文名字
#     :param itemspaceid: 广告位id
#     :return: 广告模板中文名
#     """
#     templates = {
#         '12224': {
#             'description': '开屏广告',
#             'apploading': '静图模板',
#             'h5_apploading': 'H5模板',
#             'h5video_apploading': '视频模板',
#             'apploading_fullscreen': '全屏静图模板',
#             'h5_apploading_fullscreen': '全屏H5模板',
#             'h5video_apploading_fullscreen': '全屏视频模板',
#             'lottie_apploading_fullscreen': '全屏lottile模板',
#             'combined_video_fullscreen': '双幕视频联动模板',
#             'h5_apploading_fullscreen_float': 'H5浮层模板',
#             'h5video_apploading_fullscreen_float': '视频浮层模板',
#             'multilevel_apploading_fullscreen': '多层卡片互动模板',
#             'multiple_apploading_fullscreen': '多卡切换模板',
#         },
#         '16431': {
#             'description': '频道浮层广告',
#             'app_floating': '浮层模板',
#             'app_floatbutton_combined': '联动浮层模板',
#             'picture_floating': '静图浮层',
#             'video_floating': '视频浮层',
#             'alphavideo_floating': '透明视频浮层',
#             'dynamic_floating': '跃动炫幕浮层'
#         },
#         '12451': {
#             'description': '信息流广告',
#             'info_pictxt': '信息流小图模板',
#             'info_bannertxt': '信息流Banner模板',
#             'info_bigpictxt': '信息流大图模板',
#             'info_video': '信息流视频模板',
#             'info_mixpictxt': '组图模板',
#             'info_mixpicdownload': '组图下载模板',
#             'info_bigpicdownload': '大图下载模版',
#             'info_picdownload': '小图下载模板',
#             'info_mixpicdial': '组图电话模板',
#             'info_bigpicdial': '大图电话模板',
#             'info_dyn_lunfan': '轮翻模板',
#             'info_dyn_shutter': '快门模板',
#             'custom_carda': '马卡龙小图模板',
#             'dynamic_window_news': '动态橱窗模板',
#             'combined_video_fullscreen': '联动loading',
#             'svoice_video': '多场景智能播报互动模板',
#             'macaroon_bigpictxt': '马卡龙大图模板',
#             'macaron_video_live': '马卡龙直播视频模板',
#             'macaron_beyondframe': '马卡龙出框模板',
#             'macaron_wea_bigpictxt': '马卡龙天气模板',
#             'macaron_iva_fullscreen': '马卡龙互动视频模板',
#             'bigpic_crossmove': '大图横滑卡片模板',
#             'macaron_optional': '趣选信息流模板',
#             'macaron_artascope': '互动万花筒模板',
#             'mixpictxt_tag': '组图_标签模板',
#             'optional_video': '点选视频模板',
#             'macaron_optional_reco': '趣选信息流升级版模板',
#             'macaron_video_vertical': '竖版视频模板',
#             'info_text_30': '文字链模板',
#             'macaron_distinguish': '真假测试题模板',
#             'waterfall_bigpictxt': '瀑布流-大图模板',
#             'multilevel_pic': '多层互动橱窗-图片模板',
#             'multilevel_video': '多层互动橱窗-视频模板',
#             'rotate_pic': '翻卡彩蛋模板',
#             'slide_card': '多层滑卡模板',
#             'dynamic_window_video': '滑卡视频模板',
#             'multi_vertical_video_card': '竖屏多卡模板'
#         },
#         '15681': {
#             'description': '文章页中插广告',
#             'info_bigpictxt': '大图模板',
#             'info_mixpictxt': '组图模板',
#             'info_bannertxt': '通栏模板',
#             'video_news': '智能播报',
#             'macaroon_bigpictxt': '马卡龙大图模板',
#         },
#         '16334': {
#             'description': '文章页尾插广告',
#             'info_bigpictxt': '大图模板',
#             'info_mixpictxt': '组图模板',
#             'info_bannertxt': '通栏模板',
#             'info_pictxt': '小图模板',
#             'info_mixpicdownload': '组图下载模板',
#             'info_bigpicdownload': '大图下载模版',
#             'info_picdownload': '小图下载模板',
#         },
#         '16336': {
#             'description': '文章悬浮button广告',
#             'float_button': '悬浮button模板',
#             'article_floating': '文章页浮层',
#             'floatbutton_combined': '文章页浮层button联动'
#         },
#         '12233': {
#             'description': '大图浏览最后一帧',
#             'apploading': '静图模板',
#         },
#         '12238': {
#             'description': '组图推荐第一条',
#             'picturetxt': '图文模板',
#         },
#         '12716': {
#             'description': '组图推荐第二条',
#             'picturetxt': '图文模板',
#         },
#         '12452': {
#             'description': '焦点图广告',
#             'info_bannertxt': '图文模板'
#         },
#         '15858': {
#             'description': '本地button广告',
#             'info_button': 'button模板'
#         },
#         '12719': {
#             'description': '文章页冠名广告',
#             'article_title': '通栏模板',
#         },
#         '12715': {
#             'description': '流内下拉',
#             'dropdown_picturetxt': '二楼模板',
#             'normal_picture_640*100': 'banner模板'
#         },
#         '16303': {
#             'description': '专题页广告',
#             'info_pictxt': '小图模板',
#         },
#         '12838': {
#             'description': '直播间冠名',
#             'picturetxt': '图文模板',
#         },
#         '13372': {
#             'description': '直播间顶部通栏',
#             'info_bannertxt': 'banner模板',
#         },
#         '13373': {
#             'description': '直播间流内',
#             'liveinfo_banner': '大图模板',
#         },
#         '16042': {
#             'description': '视频详情页',
#             'info_pictxt': '小图模板',
#             'info_mixpictxt': '组图模板',
#             'info_bannertxt': '通栏模板',
#         },
#         '16480': {
#             'description': '搜索品牌号',
#             'brand_promotion': '大图模板',
#             'brand_promotion_immersive': '沉浸式模板',
#         },
#         '16481': {
#             'description': '视频可跳过中插',
#             'skip_fullscreen': '视频模板',
#             'skip_picture': '图片模板	'
#         },
#         '16594': {
#             'description': '视频可跳过尾插',
#             'skip_fullscreen': '视频模板',
#             'skip_picture': '图片模板	'
#         },
#         '16605': {
#             'description': '24小时置顶广告',
#             '24hours_banner': 'banner模板'
#         },
#         '16614': {
#             'description': '负一屏速览',
#             'newscard_nativepic': '大图模板',
#             'newscard_nativevideo': '视频模板',
#         },
#         '16627': {
#             'description': '话题尾插',
#             'info_bigpictxt': '大图模板',
#             'info_mixpictxt': '组图模板',
#             'info_pictxt': '小图模板',
#         },
#         '16710': {
#             'description': '听书播放页信息流',
#             'info_mixpictxt': '组图模板',
#             'info_pictxt': '小图模板',
#         },
#         '16713': {
#             'description': '听书专辑页信息流',
#             'info_mixpictxt': '组图模板',
#             'info_pictxt': '小图模板',
#         },
#         '16711': {
#             'description': '听书视频前贴',
#             'info_skip_video': '视频模板',
#         },
#         '16714': {
#             'description': '听书视频中插',
#             'info_skip_video': '视频模板',
#         },
#     }
#     templates['13016'] = templates['12451']
#     templates['13015'] = templates['12452']
#     templates['13370'] = templates['12715']
#     templates['13371'] = templates['12716']
#
#     if itemspaceid in templates:
#         adv_templates = templates[itemspaceid]
#         if ad_template in adv_templates:
#             return f"{itemspaceid}-{adv_templates['description']}-{adv_templates[ad_template]}"
#
#     return "找不到对应的广告信息"
#
#
# # 调用示例
# english_name = 'apploading'
# ad = '12224'
# chinese_name = get_ad_template_name(english_name, ad)
# print(chinese_name)  # 输出：h5模板

# 遍历第一行的每个单元格


# import openpyxl
#
# # 指定文件路径和sheet名字
# filename = 'C:\\Users\\wenmanwei\\PycharmProjects\\AutoCheckParams\\CheckParams\\ReportDataExcel\\iOS_Report__Params - test.xlsx'
# sheet_name = '12224'
#
# # 打开Excel文件
# workbook = openpyxl.load_workbook(filename)
# sheet = workbook[sheet_name]
#
# # 获取第一行中所有单元格的值
# first_row_values = [cell.value for cell in sheet[1]]
# print(first_row_values)
#
# # 查找v所在的列数
# v_column_index = first_row_values.index('v') + 1
#
# print(v_column_index)
# # 初始化列表
# special_list = []
# all_list = []
# import_list = []
#
# # 获取第二行中特定列的值
# if sheet.cell(row=2, column=v_column_index - 1).value == 'special':
#     for row in sheet.iter_rows(min_row=3, max_row=sheet.max_row, min_col=v_column_index, max_col=v_column_index + 2):
#         special_list.append(row[0].value)
# elif sheet.cell(row=2, column=v_column_index - 1).value == 'all':
#     for row in sheet.iter_rows(min_row=3, max_row=sheet.max_row, min_col=v_column_index, max_col=v_column_index + 2):
#         all_list.append(row[0].value)
# elif sheet.cell(row=2, column=v_column_index - 1).value == 'import':
#     for row in sheet.iter_rows(min_row=3, max_row=sheet.max_row, min_col=v_column_index, max_col=v_column_index + 2):
#         import_list.append(row[0].value)
#
# # 输出结果
# print("special list:", special_list)
# print("all list:", all_list)
# print("import list:", import_list)

# import xlrd
# workbook = xlrd.open_workbook(filename=r'C:\Users\wenmanwei\PycharmProjects\AutoCheckParams\CheckParams\ReportDataExcel\iOS_Report__Params - test.xlsx')
# sheet = workbook['12224']
# merged = sheet.merged_cells
# print(merged)


# import openpyxl
#
# workbook = openpyxl.load_workbook(r'C:\Users\wenmanwei\PycharmProjects\AutoCheckParams\CheckParams\ReportDataExcel\iOS_Report__Params - test.xlsx')
# sheet = workbook.active
#
# merged_cells_ranges = sheet.merged_cells.ranges
# print(merged_cells_ranges)
#
# merged_cells = []
#
# for merged_cell_range in merged_cells_ranges:
#     merged_cells.append(merged_cell_range)
#
# for merged_cell in merged_cells:
#     start_row, start_column, end_row, end_column = merged_cell.min_row, merged_cell.min_col, merged_cell.max_row, merged_cell.max_col
#     merged_cell_value = sheet.cell(row=start_row, column=start_column).value
#     print(f"Merged cell value: {merged_cell_value}")
#
# workbook.close()

# def get_data_from_excel(all_params_filepath, itemspaceid, reporttype):
#     """
#     从Excel文件中获取指定广告位和上报类型对应的全部参数列表
#
#     :param all_params_filepath: Excel文件路径
#     :param itemspaceid: 广告位ID，用于获取对应的工作表
#     :param reporttype: 上报类型，例如 "v" 或 "av"
#     :return: 全部参数列表
#     """
#     # 打开储存双端广告位+上报类型对应的全部参数的Excel文件
#     workbook = openpyxl.load_workbook(all_params_filepath)
#     # 获取指定的工作表
#     try:
#         # 表里将不同的广告位分分成不同的sheet，根据传入的itemspaceid获取对应sheet表
#         worksheet = workbook[itemspaceid]
#         # 获取工作表的最大列数和行数
#         max_column = worksheet.max_column
#         max_row = worksheet.max_row
#         # 初始化数组，用于存储全部参数
#         all_params = []
#         # 遍历第一行的每个单元格
#         for column in range(1, max_column + 1):
#             # 获取第一行的每个单元格的值
#             cell_value = worksheet.cell(row=1, column=column).value
#             # 如果单元格的值等于上报类型
#             if cell_value == reporttype:
#                 report_type_column = column  # 获取上报类型所在列的索引
#                 print(report_type_column)
#                 break
#         # 遍历从第二行开始的每一行
#         for row in range(2, max_row + 1):
#             # 获取当前上报类型所在列的全部值
#             cell_value = worksheet.cell(row=row, column=report_type_column).value
#             # 将单元格里不为空的值都存在all_params数组内
#             if cell_value is not None:
#                 all_params.append(cell_value)
#         # 关闭Excel文件
#         workbook.close()
#         return all_params
#     except KeyError:
#         return []
#
#
# all_params_filepath = r'C:\Users\wenmanwei\PycharmProjects\AutoCheckParams\CheckParams\ReportDataExcel\iOS_Report__Params - test.xlsx'
# itemspaceid = '12224'
# reporttype = 'av'
#
# a = get_data_from_excel(all_params_filepath,itemspaceid,reporttype)
# print(a)


# import openpyxl
#
#
# def read_excel_data(file_path, sheet_name):
#     wb = openpyxl.load_workbook(file_path)
#     ws = wb[sheet_name]
#
#     # 获取首行合并单元格是否有"v"
#     headers = [cell.value for cell in ws[1] if cell.value]
#     merged_cells = ws.merged_cells.ranges
#     v_merged_cells = [merged_cell for merged_cell in merged_cells if
#                       any('v' in header for header in headers[merged_cell.min_col - 1:merged_cell.max_col])]
#
#     if len(v_merged_cells) == 0:
#         return None
#
#     # 读取v单元格对应的3列数据
#     result = {'speciallist': [], 'allist': [], 'importlist': []}
#
#     for merged_cell in v_merged_cells:
#         column = []
#         column_letter = openpyxl.utils.cell.get_column_letter(merged_cell.min_col)
#
#         for row in range(3, ws.max_row + 1):
#             cell_value = ws[column_letter + str(row)].value
#             if cell_value is not None:
#                 column.append(cell_value)
#
#         # 判断第二行单元格的值
#         value = ws[column_letter + '2'].value
#         if value == 'special':
#             result['speciallist'].extend(column)
#         elif value == 'all':
#             result['allist'].extend(column)
#         else:
#             result['importlist'].extend(column)
#
#     return result


# 使用示例
# file_path = r'C:\Users\wenmanwei\PycharmProjects\AutoCheckParams\CheckParams\ReportDataExcel\iOS_Report__Params - test.xlsx'
# sheet_name = '12224'
#
# result = read_excel_data(file_path, sheet_name)
#
# if result is not None:
#     print('speciallist:', result['speciallist'])
#     print('allist:', result['allist'])
#     print('importlist:', result['importlist'])
# else:
#     print('Excel表格中没有找到包含v的合并单元格')


# import openpyxl
#
#
# def read_excel_data(file_path, sheet_name):
#     wb = openpyxl.load_workbook(file_path)
#     ws = wb[sheet_name]
#
#     # 获取第一行全体合并单元格
#     merged_cells = ws.merged_cells.ranges
#     v_merged_cell = None
#
#     for merged_cell in merged_cells:
#         if merged_cell.min_row == 1 and merged_cell.max_row == 1:
#             v_merged_cell = merged_cell
#             break
#
#     if not v_merged_cell:
#         return None
#
#     # 获取合并单元格中每列的数据
#     result = {'speciallist': [], 'allist': [], 'importlist': []}
#
#     for col in range(v_merged_cell.min_col, v_merged_cell.max_col + 1):
#
#         column_letter = openpyxl.utils.cell.get_column_letter(col)
#
#         for row in range(3, ws.max_row + 1):
#             cell_value = ws[column_letter + str(row)].value
#             if cell_value is not None:
#                 if col == v_merged_cell.min_col:
#                     result['allist'].append(cell_value)
#                 elif col == v_merged_cell.min_col + 1:
#                     result['importlist'].append(cell_value)
#                 else:
#                     result['speciallist'].append(cell_value)
#
#     return result

# import openpyxl
#
#
# def read_excel_data(file_path, sheet_name, search_value):
#     wb = openpyxl.load_workbook(file_path)
#     ws = wb[sheet_name]
#
#     # 判断第一行是否存在合并单元格，且值等于用户输入的search_value
#     merged_cells = ws.merged_cells.ranges
#     v_merged_cell = None
#
#     for merged_cell in merged_cells:
#         if merged_cell.min_row == 1 and merged_cell.max_row == 1 and ws[merged_cell.min_row][
#             merged_cell.min_col].value == search_value:
#             v_merged_cell = merged_cell
#             break
#
#     if not v_merged_cell:
#         return None
#
#     # 获取合并单元格中每列的数据
#     result = {'speciallist': [], 'allist': [], 'importlist': []}
#
#     for col in range(v_merged_cell.min_col, v_merged_cell.max_col + 1):
#
#         column_letter = openpyxl.utils.cell.get_column_letter(col)
#
#         for row in range(3, ws.max_row + 1):
#             cell_value = ws[column_letter + str(row)].value
#             if cell_value is not None:
#                 if col == v_merged_cell.min_col:
#                     result['allist'].append(cell_value)
#                 elif col == v_merged_cell.min_col + 1:
#                     result['importlist'].append(cell_value)
#                 else:
#                     result['speciallist'].append(cell_value)
#
#     return result
#
#
# # 使用示例
# file_path = r"C:\Users\wenmanwei\PycharmProjects\AutoCheckParams\CheckParams\ReportDataExcel\iOS_Report__Params - test.xlsx"
# sheet_name = "12224"
# search_value = input("请输入要搜索的值（v/av/c/et）: ")
#
# result = read_excel_data(file_path, sheet_name, search_value)
#
# if result is not None:
#     print("speciallist:", result["speciallist"])
#     print("allist:", result["allist"])
#     print("importlist:", result["importlist"])
# else:
#     print("Excel 表格中没有找到合适的合并单元格")
import openpyxl
import os

# def get_column_data(file_path, sheet_name, variable):
#     # 打开Excel文件
#     workbook = openpyxl.load_workbook(file_path)
#     # 选择工作表
#     sheet = workbook[sheet_name]
#
#     # 获取第一行的单元格数据
#     headers = [cell.value for cell in sheet[1]]
#
#     # 检查变量是否存在于第一行
#     if variable in headers:
#         variable_index = headers.index(variable) + 1  # 获取变量所在列的索引
#         row_start = 2  # 数据开始的行号
#
#         # 获取变量下方的三列数据
#         all_list = []
#         special_list = []
#         import_list = []
#
#         row_end = row_start
#         while sheet.cell(row=row_end, column=variable_index).value is not None:
#             all_value = sheet.cell(row=row_end, column=variable_index + 1).value
#             special_value = sheet.cell(row=row_end, column=variable_index + 2).value
#             import_value = sheet.cell(row=row_end, column=variable_index + 3).value
#
#             all_list.append(all_value)
#             special_list.append(special_value)
#             import_list.append(import_value)
#
#             row_end += 1
#
#         # 返回三个列表
#         return all_list, special_list, import_list
#
#     else:
#         return None

# 这版本可以用
# def get_column_data(filepath, worksheet_name, report_type):
#     """
#
#     :param filepath:
#     :param worksheet_name:
#     :param report_type:
#     :return:
#     """
#     if not os.path.exists(filepath):
#         return f"文件路径不存在: {filepath}"
#     # 打开Excel文件
#     workbook = openpyxl.load_workbook(filepath)
#     # 选择工作表
#     sheet = workbook[worksheet_name]
#
#     # 获取第一行的单元格数据
#     headers = [cell.value for cell in sheet[1]]
#
#     # 检查变量是否存在于第一行
#     if report_type in headers:
#         report_type_index = headers.index(report_type) + 1  # 获取变量所在列的索引
#         row_start = 2  # 数据开始的行号
#
#         # 获取变量下方的三列数据
#         all_list = []
#         import_list = []
#         special_list = []
#
#         row_end = row_start
#         while sheet.cell(row=row_end, column=report_type_index).value is not None:
#             all_value = sheet.cell(row=row_end, column=report_type_index).value
#             import_value = sheet.cell(row=row_end, column=report_type_index + 1).value
#             special_value = sheet.cell(row=row_end, column=report_type_index + 2).value
#             if all_value is not None:
#                 all_list.append(all_value)
#             if import_value is not None:
#                 import_list.append(import_value)
#             if special_value is not None:
#                 special_list.append(special_value)
#
#             row_end += 1
#
#         # 返回三个列表
#         return all_list, import_list, special_list
#
#     else:
#         return None
#
#
# # 示例用法
# file_path = r"C:\Users\wenmanwei\PycharmProjects\AutoCheckParams\CheckParams\ReportDataExcel\iOS_Report__Params - test.xlsx"
# sheet_name = '12224'
# search_value = 'adv'
#
# result = get_column_data(file_path, sheet_name, search_value)
# if result:
#     all_list, import_list, special_list = result
#     print("all_list:", all_list)
#     print("special_list:", special_list)
#     print("import_list:", import_list)
# else:
#     result = f"未找到{search_value}上报类型的参数配置，请配置"
#     print(result)

import openpyxl
import os

# def get_params_config_from_excel(filepath, worksheet_name, report_type):
#     """
#
#     :param filepath:
#     :param worksheet_name:
#     :param report_type:
#     :return:
#     """
#     # 检查文件路径是否存在
#     if not os.path.exists(filepath):
#         return f"文件路径不存在: {filepath}"
#     # 加载工作簿和工作表
#     workbook = openpyxl.load_workbook(filepath)
#     if worksheet_name in workbook.sheetnames:
#         sheet = workbook[worksheet_name]
#         # 获取表头
#         headers = [cell.value for cell in sheet[1]]
#         # 检查上报类型是否存在于表头中
#         if report_type in headers:
#             # 创建表头索引字典 表头值与索引存在一个字典内 例如{'v': 1, None: 9, 'av': 4, 'c': 7}
#             header_index = {header: index + 1 for index, header in enumerate(headers)}
#             # 获取上报类型的列索引，比如v 列索引是1即，第1列，all是1，important是2，special是3
#             report_type_index = header_index[report_type]
#             # 确定数据行起始位置，从第3行开始读，第二行 all，import,special顺序不可变
#             row_start = 3 if sheet.max_row > 1 else 1
#             # 初始化各个列表
#             all_parameters = []
#             important_parameters = []
#             special_parameters = []
#
#             # 遍历每一行，提取对应列的数值,遍历范围从第3行开始，表格最大行结束
#             for row in range(row_start, sheet.max_row + 1):
#                 # 如果v上报列索引是1，则全部参数为第1列，从第3行开始全部列的值
#                 # sheet.cell(row=row, column=report_type_index).value，指定行row和列column返回单元格的值
#                 all_params_value = sheet.cell(row=row, column=report_type_index).value
#                 if all_params_value:
#                     all_parameters.append(all_params_value)
#                 # 如果v上报列索引是1，重要参数为第2列，从第3行开始全部列的值
#                 important_params_value = sheet.cell(row=row, column=report_type_index + 1).value
#                 if important_params_value:
#                     important_parameters.append(important_params_value)
#                 # 如果v上报列索引是1，特殊参数为第3列，从第3行开始全部列的值
#                 special_params_value = sheet.cell(row=row, column=report_type_index + 2).value
#                 if special_params_value:
#                     special_parameters.append(special_params_value)
#             # 将3个列表返回
#             return all_parameters, important_parameters, special_parameters
#         else:
#             return f"未找到{report_type}上报类型的参数配置，请配置"
#     else:
#         # 工作表不存在，给出错误提示或执行其他容错处理
#         return f"未找到{report_type}上报类型的参数配置，请配置"
#         # 其他容错处理
#
#
# # 示例用法
# file_path = r"C:\Users\wenmanwei\PycharmProjects\AutoCheckParams\CheckParams\ReportDataExcel\iOS_Report__Params - test.xlsx"
# sheet_name = '13370'  # ios or android
# search_value = 'v'  # event=1
#
# if isinstance(get_params_config_from_excel(file_path, sheet_name, search_value), str):
#     print(get_params_config_from_excel(file_path, sheet_name, search_value))
# else:
#     all_params, important_params, special_params = get_params_config_from_excel(file_path, sheet_name, search_value)
#     print("all_params:", all_params)
#     print("important_params:", important_params)
#     print("special_params:", special_params)

# important_params = ['appchn', 'appid', 'appv']
# actual_params = {'appchn': '4', 'appid': '11', 'ad_abtest': 'e1_2', 'adstyle': '1', 'apid': '12451'}
#
# imp = {param: actual_params.get(param, "重要参数缺失") for param in important_params}
#
# print(imp)

# def modify_all_params(ad_os, advertisement_id, ad_report_type, all_parameters, report_status=None, ad_template=None):
#     """
#
#     :param report_status:
#     :param ad_os:
#     :param advertisement_id:
#     :param ad_report_type:
#     :param all_parameters:
#     :param ad_template:
#     :return:
#     """
#     if ad_os == 'iOS':
#         if report_status == '0':
#             all_parameters.append('status')
#             if advertisement_id == '12224':
#                 all_parameters = [param for param in all_parameters if
#                                   param not in ['vrpttype', 'deeplink', 'monitorkey']]
#             elif advertisement_id == '16431':
#                 all_parameters = [param for param in all_parameters if
#                                   param not in ['local', 'monitorkey', 'vrpttype', 'deeplink']]
#             elif advertisement_id == '13016' or ad_id == '12451':
#                 all_parameters = [param for param in all_parameters if
#                                   param not in ['mkey', 'adstyle', 'vrpttype', 'vrpttype']]
#         else:
#             if advertisement_id == '12224' and ad_report_type == 'c':
#                 all_parameters.append('clicktype')
#
#     return all_parameters
#
#
# ad_id = '12224'
# os = 'iOS'
# all_params = ['appchn', 'appid', 'appv', 'asid', 'build_version', 'cid', 'clickmonitor', 'deeplink', 'delaytrack',
#               'errorcode', 'gbcode', 'impressionid', 'itemspaceid', 'login_pid', 'monitorkey', 'newschn', 'nwt', 'os',
#               'path', 'sdkv', 'span', 'sv', 'timetag', 'viewmonitor', 'vrpttype']
# report_type = 'c'
# status = '1'
# template = None
# print(modify_all_params(os, ad_id, report_type, all_params, status, template))

# def compare_keys(testa, testb, uevent_key):
#     expected_keys = set(testa)
#
#     result = {}
#
#     for i, item in enumerate(testb[uevent_key]["details"]):
#         actual_keys = set()
#         for key, value in item.items():
#             actual_keys.add(key)
#             if isinstance(value, dict):
#                 actual_keys.update(value.keys())
#
#         # Check missing parameters
#         missing_keys = expected_keys - actual_keys
#
#         expected = actual_keys.intersection(expected_keys)
#         # Check redundant parameters
#         redundant_keys = actual_keys - expected_keys
#         comparison_result = {
#             "缺失参数": list(missing_keys),
#             "冗余参数": list(redundant_keys)
#         }
#         missing_counts = len(missing_keys)
#         redundant_counts = len(redundant_keys)
#
#         if missing_counts > 0 or redundant_counts > 0:
#             if missing_counts > 0 and redundant_counts > 0:
#                 result[f"{uevent_key}[{i}]校验失败，存在缺失和冗余参数--->"] = comparison_result
#             if missing_counts > 0 and redundant_counts == 0:
#                 keys_to_remove = ['冗余参数']
#                 for key in keys_to_remove:
#                     comparison_result.pop(key)
#                 result[f"{uevent_key}[{i}]校验失败，存在缺失参数--->"] = comparison_result
#
#             if missing_counts == 0 and redundant_counts > 0:
#                 keys_to_remove = ['缺失参数']
#                 for key in keys_to_remove:
#                     comparison_result.pop(key)
#                 result[f"{uevent_key}[{i}]校验失败，存在冗余参数--->"] = comparison_result
#
#     return result
#
#
# testa = ["uevent", "a", "wwm", "mmm", "www", "rongyu"]
#
# testb = {
#     "uevent=7": {
#         "uevent=7事件总数为": "1",
#         "details": [
#             {
#                 "scene_type": "user",
#                 "asid": "0AA1BEB0-7F41-4D55-ACF0-72C30C6F9FFE_1698044320731",
#                 "uevent": "7",
#                 "timetag": "1698044320797",
#                 "nwt": "1001",
#                 "build_version": "7.0.60.224.1",
#                 "launch_id": "C207E641-398A-4051-8027-E8AEC1CDFBA8",
#                 "appv": "7.0.6",
#                 "seq": "1"
#             }
#         ]
#     },
#     "uevent=8": {
#         "uevent=8事件总数为": "1",
#         "details": [
#             {
#                 "scene_type": "user",
#                 "asid": "0AA1BEB0-7F41-4D55-ACF0-72C30C6F9FFE_1698044320731",
#                 "uevent": "8",
#                 "timetag": "1698044320873",
#                 "nwt": "1001",
#                 "build_version": "7.0.60.224.1",
#                 "launch_id": "C207E641-398A-4051-8027-E8AEC1CDFBA8",
#                 "appv": "7.0.6",
#                 "seq": "2"
#             }
#         ]
#     }
# }
# u = "uevent=8"
# testwwm = compare_keys(testa, testb, u)
# print(
#     testwwm)
#
# import json
# json1 = {
#         "scene_type": "mat",
#         "uevent": "24",
#         "asid": "5B6B37C0-5920-4250-92A1-01288B1D4533_1678948807724",
#         "appv": "7.0.6",
#         "build_version": "7.0.6.224.1",
#         "nwt": "1001",
#         "timetag": "1692696139738",
#         "mat_md5list": "[{\"md5\":\"bec3774c8942b70d0360f6422397f8d7\",\"time\":\"1695282390000\",\"type\":\"\"},{\"md5\":\"e775f3d23adbb4227dfe535df7bc1217\",\"time\":\"1691668132000\",\"type\":\"\"},{\"md5\":\"be9571f00b48aa1924dea4ca465af62c\",\"time\":\"1697603567616\",\"type\":\"png\"},{\"md5\":\"7d0ac15343c819e8fa2a2d5abcd11f34\",\"time\":\"1697603567580\",\"type\":\"mp4\"}]"
#     }
# jsonstr = json.dumps(json1)
# my_dict = json.loads(jsonstr)
#
# sorted_keys = sorted(my_dict.keys())
#
# print("排序后的键：")
# for key in sorted_keys:
#     print(key)

import re

version_string = 'iOS7.0.1'
pattern = r'iOS\s*\d+\.\d+\.\d+'
matches = re.match(pattern, version_string)

if matches:

    print("可以匹配:")
else:
    print("未找到匹配的模式")


